create or replace view vac_application_auth as
with au as
(
select
 workspace,
 application_id,
 application_name,
 is_subscribed,
 authentication_scheme_name,
 description,
--
 ac_utils.hs(page_sentry_function) page_sentry_function_hs,
 dbms_lob.substr(page_sentry_function,4000, 1)        page_sentry_function_sr1,
 dbms_lob.substr(page_sentry_function,4000, 4000+1 )  page_sentry_function_sr2,
 dbms_lob.substr(page_sentry_function,4000, 8000+1 )  page_sentry_function_sr3,
 dbms_lob.substr(page_sentry_function,4000, 12000+1)  page_sentry_function_sr4,
--
 ac_utils.hs(session_verify_function) session_verify_function_hs,
 dbms_lob.substr(session_verify_function,4000, 1)        session_verify_function_sr1,
 dbms_lob.substr(session_verify_function,4000, 4000+1 )  session_verify_function_sr2,
 dbms_lob.substr(session_verify_function,4000, 8000+1 )  session_verify_function_sr3,
 dbms_lob.substr(session_verify_function,4000, 12000+1)  session_verify_function_sr4,
 to_char(invalid_session_page) invalid_session_page,
 invalid_session_url,
--
 ac_utils.hs(pre_authentication_process) pre_authentication_process_hs,
 dbms_lob.substr(pre_authentication_process,4000,1)      pre_authentication_process_sr1,
 dbms_lob.substr(pre_authentication_process,4000,4000+1) pre_authentication_process_sr2,
 dbms_lob.substr(pre_authentication_process,4000,8000+1) pre_authentication_process_sr3,
 dbms_lob.substr(pre_authentication_process,4000,1200+1) pre_authentication_process_sr4,
--
 ac_utils.hs(authentication_function) authentication_function_hs,
 dbms_lob.substr(authentication_function,4000,1)      authentication_function_sr1,
 dbms_lob.substr(authentication_function,4000,4000+1) authentication_function_sr2,
 dbms_lob.substr(authentication_function,4000,8000+1) authentication_function_sr3,
 dbms_lob.substr(authentication_function,4000,1200+1) authentication_function_sr4,
--
 ac_utils.hs(post_authentication_process) post_authentication_process_hs,
 dbms_lob.substr(post_authentication_process,4000,1)      post_authentication_process_s1,
 dbms_lob.substr(post_authentication_process,4000,4000+1) post_authentication_process_s2,
 dbms_lob.substr(post_authentication_process,4000,8000+1) post_authentication_process_s3,
 dbms_lob.substr(post_authentication_process,4000,1200+1) post_authentication_process_s4,
--
 cookie_name,
 cookie_path,
 cookie_domain,
 cookie_secure,
 ldap_host,
 ldap_port,
 ldap_dn_string,
 --ldap_username_edit_function,
 logout_url,
 help_text,
 last_updated_by,
 last_updated_on,
 authentication_scheme_id,
 referenced_schema_id,
 component_signature
from
APEX_APPLICATION_AUTH t
)
select
  workspace,
  application_id,
  application_name,
  authentication_scheme_name,
--
  to_clob(page_sentry_function_sr1)   || to_clob(page_sentry_function_sr2)
  || to_clob(page_sentry_function_sr3)|| to_clob(page_sentry_function_sr4)  page_sentry_function_sr,
--
  to_clob(pre_authentication_process_sr1)    || to_clob(pre_authentication_process_sr2)
  || to_clob(pre_authentication_process_sr3) || to_clob(pre_authentication_process_sr4) pre_authentication_process_sr,
--
  to_clob(session_verify_function_sr1)      || to_clob(session_verify_function_sr2)
  || to_clob(  session_verify_function_sr3) || to_clob(session_verify_function_sr4) session_verify_function_sr,
--
  to_clob(post_authentication_process_s1)    || to_clob(post_authentication_process_s2)
  || to_clob(post_authentication_process_s3) || to_clob(post_authentication_process_s4) post_authentication_process_sr,
--
  to_clob(authentication_function_sr1)    || to_clob(authentication_function_sr2)
  || to_clob(authentication_function_sr3) || to_clob(authentication_function_sr4) authentication_function_sr,
--
  authentication_scheme_id,
  last_updated_by,
  last_updated_on,
  'APEX_APPLICATION_AUTH' src_view_name,
  u_name,
  u_value
from
(
select
  workspace,
  application_id,
  application_name,
  authentication_scheme_name,
--
  page_sentry_function_sr1,
  page_sentry_function_sr2,
  page_sentry_function_sr3,
  page_sentry_function_sr4,
--
  pre_authentication_process_sr1,
  pre_authentication_process_sr2,
  pre_authentication_process_sr3,
  pre_authentication_process_sr4,
--
  session_verify_function_sr1,
  session_verify_function_sr2,
  session_verify_function_sr3,
  session_verify_function_sr4,
--
  post_authentication_process_s1,
  post_authentication_process_s2,
  post_authentication_process_s3,
  post_authentication_process_s4,
--
  authentication_function_sr1,
  authentication_function_sr2,
  authentication_function_sr3,
  authentication_function_sr4,
--
  authentication_scheme_id,
  last_updated_by,
  last_updated_on,
  'APEX_APPLICATION_AUTH' src_view_name,
  u_name,
  u_value
from au
 UNPIVOT INCLUDE NULLS ( u_value
   FOR    u_name
   IN
   (
     is_subscribed,
     description,
     page_sentry_function_hs,
     session_verify_function_hs,
     invalid_session_page,
     invalid_session_url,
     pre_authentication_process_hs,
     authentication_function_hs,
     post_authentication_process_hs,
     cookie_name,
     cookie_path,
     cookie_domain,
     cookie_secure,
     ldap_host,
     ldap_port,
     ldap_dn_string,
   --  ldap_username_edit_function,
     logout_url,
     help_text
   )
 )
);

